﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace MISAPP.Classify.DataAccess
{
    public class DA_Q1 : DA_Matrix
    {
        public DA_Q1()
        {
            tableName = "bonus";
        }
        protected bool setTableSchema = false;

        public bool Insert(string p_misGroup, string p_cell_value
            , string p_user_id, string p_row_pos, string p_sheetname)
        {
            if (setTableSchema == false)
            {
                setTableSchema = true;
                FillTableSchema(tableName);
            }
            whereList = new List<string>();
            fieldList = new List<field>();
            string sql = @"
INSERT INTO BONUS(misgroup,cell_value,init_time,user_id,row_pos,sheetname)
VALUES(@p_misGroup,@p_cell_value,GETDATE(),@p_user_id,@p_row_pos,@p_sheetName)
";

            FillFieldList(new string[] { p_misGroup }, new string[] { "@p_misGroup" }, "misgroup");
            FillFieldList(new string[] { p_cell_value }, new string[] { "@p_cell_value" }, "cell_value");
            FillFieldList(new string[] { p_user_id }, new string[] { "@p_user_id" }, "user_id");
            FillFieldList(new string[] { p_row_pos }, new string[] { "@p_row_pos" }, "row_pos");
            FillFieldList(new string[] { p_sheetname }, new string[] { "@p_sheetname" }, "sheetname");
            AddParameter(sql);
            int iResult = Common.DAL.ExecuteNoneQuery(cmd);
            return iResult < 0 ? false : true;

        }

        public DataTable GetDataBySheet(string p_sheetName)
        {
            string sql = string.Format(@"
SELECT 
		A.ROW_POS
		,A.cell_value
		,A.SHEETNAME
		,A.MISGroup
	FROM(
		SELECT
			*
			,ROW_NUMBER() OVER(PARTITION BY A.ROW_POS,A.MISGroup ORDER BY A.INIT_Time DESC) AS SEQ
		FROM bonus AS A 
        WHERE A.SHEETNAME = '{0}'
		)A
	WHERE A.SEQ = 1	
", p_sheetName);
            return getData(sql, p_sheetName);
        }

        public DataTable GetSquaredAreaByLevel(string p_level)
        {
            return GetSquaredAreaByLevel("Q1", p_level);
        }
    }
}

